{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 安装与使用\n",
    "安装python 驱动器： pip install psycopg2        \n",
    "\n",
    "启动server：pg_ctlcluster 11 main start      \n",
    "\n",
    "su - postgres\n",
    "\n",
    "psql\n",
    "\n",
    "\\password postgres 设置密码"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import psycopg2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn=psycopg2.connect(database=\"postgres\",user=\"postgres\",password=\"123456\",host=\"localhost\",port=\"5432\")\n",
    "cur=conn.cursor() #创建指针对象"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建表\n",
    "cur.execute(\"CREATE TABLE student(id integer,name varchar,sex varchar);\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "#插入数据\n",
    "cur.execute(\"INSERT INTO student(id,name,sex)VALUES(%s,%s,%s)\",(1,'Aspirin','M'))\n",
    "cur.execute(\"INSERT INTO student(id,name,sex)VALUES(%s,%s,%s)\",(2,'Taxol','F'))\n",
    "cur.execute(\"INSERT INTO student(id,name,sex)VALUES(%s,%s,%s)\",(3,'Dixheral','M'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(1, 'Aspirin', 'M'), (2, 'Taxol', 'F'), (3, 'Dixheral', 'M')]\n"
     ]
    }
   ],
   "source": [
    "# 获取结果\n",
    "cur.execute('SELECT * FROM student')\n",
    "results=cur.fetchall()\n",
    "print (results)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 关闭连接\n",
    "conn.commit()\n",
    "cur.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 创建testdb 与 dbuser1 \n",
    "Create User dbuser1 WITH PASSWORD '123456';\n",
    "\n",
    "Create DATABASE testdb OWNER dbuser1;\n",
    "\n",
    "GRANT ALL PRIVILEGES ON DATABASE testdb TO dbuser1;\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn=psycopg2.connect(database=\"testdb\",user=\"dbuser1\",password=\"123456\",host=\"localhost\",port=\"5432\")\n",
    "cur=conn.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('PostgreSQL 11.5 (Ubuntu 11.5-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit',)]\n"
     ]
    }
   ],
   "source": [
    "cur.execute('select version();')\n",
    "results=cur.fetchall()\n",
    "print (results)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'CREATE TABLE COMPANY        (ID INT PRIMARY KEY     NOT NULL,       NAME           TEXT    NOT NULL,       AGE            INT     NOT NULL,       ADDRESS        CHAR(50),       SALARY         REAL);'"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 创建表, 注意多行字符串\n",
    "creates = \"CREATE TABLE COMPANY \\\n",
    "       (ID INT PRIMARY KEY     NOT NULL,\\\n",
    "       NAME           TEXT    NOT NULL,\\\n",
    "       AGE            INT     NOT NULL,\\\n",
    "       ADDRESS        CHAR(50),\\\n",
    "       SALARY         REAL);\"\n",
    "creates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.execute(creates)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.execute(\"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 );\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(1, 'Paul', 'California                                        ', 20000.0)]\n"
     ]
    }
   ],
   "source": [
    "cur.execute(\"SELECT id, name, address, salary  from COMPANY\")\n",
    "results=cur.fetchall()\n",
    "print (results)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.commit()\n",
    "cur.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
